﻿using Hxj.Data;
using RecruitStudents.Utility;
using RecruitStudents.Utility.Helper;
using RecruitStudents.Utility.Json;
using RecruitStudents.Utility.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RecruitStudents.Client.DAL
{
    public class DALComm : Base.BaseService
    {
        public DataTable GetRoleAuthModuleTable(string strRoleID)
        {
            return DbHelper.GetDatabase().From<TB_Sys_Module>() //选择表 
                                       .InnerJoin<TB_Sys_RoleAuth>(TB_Sys_RoleAuth._.ModuleID == TB_Sys_Module._.ModuleID)
                                       .Select(TB_Sys_Module._.All)  //设置选择的字段数目 
                                       .Where(TB_Sys_RoleAuth._.RoleID == strRoleID)
                                       .ToDataTable();   //返回List
        }

        public string CreateTableAppNo(string strTableName, string strExamYear)
        {
            string strValue = string.Empty;
            DbParameter[] parameters = new DbParameter[]
                {
                    new SqlParameter("@TableName", SqlDbType.NVarChar,50),
                    new SqlParameter("@ExamYear",SqlDbType.NVarChar,5)
                };
            parameters[0].Value = strTableName;
            parameters[1].Value = strExamYear;
            strValue = DbHelper.GetDatabase().FromProc("PRO_TableAppNo").AddParameter(parameters).ToScalar<string>();
            return strValue;
        }

        public DataTable GetExamLevelTable()
        {
            return DbHelper.GetDatabase().From<TB_Dic_ExamLevel>().Where(TB_Dic_ExamLevel._.IsActive == "1").ToDataTable();
        }


        /// <summary>
        /// 根据身份证号码解析出出生日期，性别 
        /// </summary>
        /// <param name="strIdentityCard"></param>
        /// <returns></returns>
        public List<string> GetBirthSexByIdCard(string strIdentityCard)
        {
            List<string> list = new List<string>();
            string strBirth = "";
            string strSex = "";
            if (strIdentityCard.Trim().Length == 18 || strIdentityCard.Trim().Length == 15)
            {
                if (strIdentityCard.Trim().Length == 18)
                {
                    strBirth = strIdentityCard.Trim().Substring(6, 4) + "-" + strIdentityCard.Trim().Substring(10, 2) + "-" + strIdentityCard.Trim().Substring(12, 2);
                    if ("13579".IndexOf(strIdentityCard.Trim().Substring(16, 1)) > -1)
                    {
                        strSex = "1";//男
                    }
                    else
                    {
                        strSex = "2";//女
                    }
                }
                else
                {
                    strBirth = "19" + strIdentityCard.Trim().Substring(6, 2) + "-" + strIdentityCard.Trim().Substring(8, 2) + "-" + strIdentityCard.Trim().Substring(10, 2);
                    if ("13579".IndexOf(strIdentityCard.Trim().Substring(14, 1)) > -1)
                    {
                        strSex = "1";
                    }
                    else
                    {
                        strSex = "2";
                    }
                }
                list.Add(strBirth);
                list.Add(strSex);
            }
            return list;
        }

        /// <summary>
        /// 根据高考号获取生源地,如果解析出一个生源地只读，大于一个可以让用户来进行选择报考
        /// </summary>
        /// <param name="strNMT_SignUpID"></param>
        /// <returns></returns>
        public DataTable GetProviceTable(string strNMT_SignUpID)
        {
            DataTable dtResult = new DataTable();
            dtResult.Columns.Add(new DataColumn("ProviceNo", typeof(string)));
            dtResult.Columns.Add(new DataColumn("ProviceName", typeof(string)));
            dtResult.Columns.Add(new DataColumn("Message", typeof(string)));
            if (strNMT_SignUpID.Trim().Length != 10 && strNMT_SignUpID.Trim().Length != 11 && strNMT_SignUpID.Trim().Length != 14)
            {
                DataRow dr = dtResult.NewRow();
                dr["Message"] = "高考报名号【位数】不正确！";
                dtResult.Rows.Add(dr);
            }
            else
            {
                WhereClip clip = new WhereClip();
                clip = clip.And(TB_SignUpStudents._.NMT_SignUpID == strNMT_SignUpID);
                var dt = DbHelper.GetDatabase().From<TB_SignUpStudents>()                 //选择表 
                                           .Select(TB_SignUpStudents._.NMT_SignUpID)    //设置选择的字段数目 
                                           .Where(clip)                                  //条件
                                           .ToDataTable();                              //返回DataTable
                if (dt.Rows.Count == 0)
                {
                    dtResult = CheckNMT_SignUpID(strNMT_SignUpID);
                    if (dtResult == null)
                    {
                        DataRow dr = dtResult.NewRow();
                        dr["Message"] = "高考报名号格式不正确，无法解析生源地！";
                        dtResult.Rows.Add(dr);
                    }
                }
                else
                {
                    DataRow dr = dtResult.NewRow();
                    dr["Message"] = "该高考报名号已经存在，请确认！";
                    dtResult.Rows.Add(dr);
                }
            }
            return dtResult;
        }

        /// <summary>
        /// 根据高考号获取生源地
        /// </summary>
        /// <param name="strNMT_SignUpID"></param>
        /// <returns></returns>
        public DataTable CheckNMT_SignUpID(string strNMT_SignUpID)
        {
            DataTable dtResult = new DataTable();
            dtResult.Columns.Add(new DataColumn("ProviceNo", typeof(string)));
            dtResult.Columns.Add(new DataColumn("ProviceName", typeof(string)));
            dtResult.Columns.Add(new DataColumn("Message", typeof(string)));
            try
            {
                DataTable dtArea = DbHelper.GetDatabase().From<TB_Dic_ExamProvince>() //选择表
                                       .LeftJoin<TB_Dic_NMT_SignUp>(TB_Dic_ExamProvince._.DicNO == TB_Dic_NMT_SignUp._.NativePlaceNO)
                                       .Select(TB_Dic_ExamProvince._.All, TB_Dic_NMT_SignUp._.Index_Begin, TB_Dic_NMT_SignUp._.Index_End, TB_Dic_NMT_SignUp._.Mask, TB_Dic_NMT_SignUp._.NMT_SignUp_Len, TB_Dic_NMT_SignUp._.NativePlaceNO, TB_Dic_NMT_SignUp._.NativePlaceName)  //设置选择的字段数目 
                                       .Where(TB_Dic_ExamProvince._.IsActive == "1")
                                       .ToDataTable();   //返回List
                if (dtArea != null && dtArea.Rows.Count > 0)
                {
                    foreach (DataRow dr in dtArea.Rows)
                    {
                        if (dr["NMT_SignUp_Len"].ToSafeString().ToSafeInt() == strNMT_SignUpID.Trim().Length)
                        {
                            if (dr["Index_Begin"].ToSafeString().ToSafeInt() == 0 && dr["Index_End"].ToSafeString().ToSafeInt() == 0)
                            {
                                DataRow drNew = dtResult.NewRow();
                                drNew["ProviceNo"] = dr["NativePlaceNO"].ToSafeString();
                                drNew["ProviceName"] = dr["DicName"].ToSafeString();
                                drNew["Message"] = string.Empty;
                                dtResult.Rows.Add(drNew);
                            }
                            else
                            {
                                string strMask = strNMT_SignUpID.Trim().Substring(dr["Index_Begin"].ToSafeString().ToSafeInt() - 1, dr["Index_End"].ToSafeString().ToSafeInt());
                                if (dr["Mask"].ToSafeString() == strMask)
                                {
                                    DataRow drNew = dtResult.NewRow();
                                    drNew["ProviceNo"] = dr["NativePlaceNO"].ToSafeString();
                                    drNew["ProviceName"] = dr["DicName"].ToSafeString();
                                    drNew["Message"] = string.Empty;
                                    dtResult.Rows.Add(drNew);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                dtResult = null;
            }
            return dtResult;
        }

        /// <summary>
        /// 获取字典表维护
        /// </summary>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        public DataTable GetDicDataTableByDicTableName(string strTableName)
        {
            string sqlString = string.Format("select * from {0}  where IsActive=1", strTableName.RemoveSqlInjection());
            return DbHelper.GetDatabase().FromSql(sqlString).ToDataTable();
        }

        /// <summary>
        /// 获取所有考点
        /// </summary>
        /// <returns></returns>
        public DataTable GetExamPlaceTable()
        {
            return DbHelper.GetDatabase().From<TB_Sys_ExamPlace>().Where(TB_Sys_ExamPlace._.IsActive == "1").ToDataTable();
        }

        /// <summary>
        /// 获取考点列表（报名专用，需要用生源地过滤）
        /// </summary>
        /// <returns></returns>
        public DataTable GetExamPlaceTable(string strExamProvince)
        {
            WhereClip clip = new WhereClip();
            clip = clip.And(TB_Sys_ExamPlace._.IsActive == "1");
            clip = clip.And(TB_Sys_ExamPlace._.ExamPlaceNO.SubQueryNotIn(DbHelper.GetDatabase().From<TB_Sys_ExamPlace_Limit>().Select(TB_Sys_ExamPlace_Limit._.ExamPlaceNO).Where(TB_Sys_ExamPlace_Limit._.ExamProvince == strExamProvince)));

            return DbHelper.GetDatabase().From<TB_Sys_ExamPlace>() //选择表
                                       .Select(TB_Sys_ExamPlace._.ExamPlaceNO, TB_Sys_ExamPlace._.ExamPlaceName)  //设置选择的字段数目 
                                       .Where(clip)   //条件
                                       .ToDataTable();   //返回List
        }

        /// <summary>
        /// 根据考点获取专业（报名专用，需要用性别过滤）
        /// </summary>
        /// <param name="strExamPlaceID"></param>
        /// <returns></returns>
        public DataTable GetSpecialityKindsTableByExamPlaceID(string strExamPlaceID, string strSex)
        {
            WhereClip clip = new WhereClip();
            clip = clip.And(TB_Sys_SpecialityKinds._.IsActive == "1");
            clip = clip.And(TB_Sys_SpecialityKinds._.LimitSex == strSex || TB_Sys_SpecialityKinds._.LimitSex == "0");
            clip = clip.And(TB_Sys_SpecialityKinds._.SpecialityKindsNO.SubQueryIn(DbHelper.GetDatabase().From<TB_Sys_ExamPlaceSpecialityKindsMapping>().Select(TB_Sys_ExamPlaceSpecialityKindsMapping._.SpecialityKindsNO)
                .Where(TB_Sys_ExamPlaceSpecialityKindsMapping._.ExamPlaceNO == strExamPlaceID)));

            return DbHelper.GetDatabase().From<TB_Sys_SpecialityKinds>() //选择表
                                       .Select(TB_Sys_SpecialityKinds._.SpecialityKindsNO, TB_Sys_SpecialityKinds._.SpecialityKindsName)  //设置选择的字段数目 
                                       .Where(clip)   //条件
                                       .OrderBy(new OrderByClip(TB_Sys_SpecialityKinds._.Sort, OrderByOperater.ASC))   //排序
                                       .ToDataTable();   //返回List
        }

        /// <summary>
        /// 根据考点,专业,级别获取分组  兼容报名获取初试考试时间   
        /// </summary>
        /// <param name="examPlaceNo"></param>
        /// <param name="specialityKindsNo"></param>
        /// <param name="examLevel"></param>
        /// <returns></returns>
        public DataTable GetAppointmentGroupDataTable(string strExamPlaceNO, string strSpecialityKindsNO, string strExamLevel)
        {
            WhereClip where = new WhereClip();
            if (!strExamPlaceNO.IsNullOrEmptyString())
            {
                where = where.And(TB_Sys_ExamPlaceSpecialityKindsGroupMapping._.ExamPlaceNO == strExamPlaceNO);
            }
            if (!strExamLevel.IsNullOrEmptyString())
            {
                where = where.And(TB_Sys_ExamPlaceSpecialityKindsGroupMapping._.ExamLevel == strExamLevel);
            }
            if (!strSpecialityKindsNO.IsNullOrEmptyString())
            {
                where = where.And(TB_Sys_ExamPlaceSpecialityKindsGroupMapping._.SpecialityKindsNO.SelectIn(strSpecialityKindsNO.Split(',')));
            }
            return DbHelper.GetDatabase().From<TB_Sys_ExamPlaceSpecialityKindsGroupMapping>().Where(where).ToDataTable();
        }

        /// <summary>
        /// 验证身份证号或高考报名号是否存在
        /// </summary>
        /// <param name="strIdCard">身份证号</param>
        /// <param name="strNMT_SignUpID">高考报名号</param>
        /// <param name="strFlag">0验证身份证号，1验证高考报名号</param>
        /// <returns></returns>
        public JsonMessage StudentInfoModify_CheckIsRegist(string strIdCard, string strNMT_SignUpID, string strFlag)
        {
            JsonMessage json = new JsonMessage();
            TB_SignUpStudents model = null;
            if (strFlag == "0")
            {
                model = DbHelper.GetDatabase().From<TB_SignUpStudents>().Where(TB_SignUpStudents._.IdentityCard == strIdCard).ToFirst();
            }
            else if (strFlag == "1")
            {
                model = DbHelper.GetDatabase().From<TB_SignUpStudents>().Where(TB_SignUpStudents._.NMT_SignUpID == strNMT_SignUpID).ToFirst();
            }
            if (model == null)
            {
                json.State = "true";
            }
            else
            {
                json.State = "false";
                if (strFlag == "0")
                {
                    json.Msg = "身份证号已经存在";
                }
                else if (strFlag == "1")
                {
                    json.Msg = "高考报名号已经存在";
                }
            }
            return json;
        }

        /// <summary>
        /// 根据考点和专业生成准考证号
        /// </summary>
        /// <param name="strExamPlaceID"></param>
        /// <param name="strSpecialityKindsNO"></param>
        /// <returns></returns>
        public string CreateExamAllowExamID(string strExamPlaceID, string strSpecialityKindsNO, string strExamYear)
        {
            string strAppNo = CreateExamAllowExamIdData(strExamPlaceID, strSpecialityKindsNO, strExamYear);
            strExamYear = strExamYear.Substring(strExamYear.Length - 2);
            if (strExamPlaceID.Length > 2)
            {
                strExamPlaceID = strExamPlaceID.Substring(0, 2);
            }
            strAppNo = strExamYear + strExamPlaceID + strSpecialityKindsNO + strAppNo.PadLeft(4, '0');
            return strAppNo;
        }
        /// <summary>
        /// 生成订单编号
        /// </summary>
        /// <param name="strExamPlaceID"></param>
        /// <param name="strSpecialityKindsNO"></param>
        /// <returns></returns>
        public string CreateOrderNo()
        {
            string strAppNo = string.Empty;
            using (DataTable dt = CreateOrderNoData())
            {
                if (dt.Rows.Count > 0)
                {
                    string strExamYear = dt.Rows[0]["ExamYear"].ToString();
                    string strSerialNumber = dt.Rows[0]["SerialNumber"].ToString();
                    strAppNo = strExamYear + strSerialNumber.PadLeft(5, '0');
                    return strAppNo;
                }
            }
            return CreateOrderNo();
        }

        /// <summary>
        /// 生成顺序抽签号
        /// </summary>
        /// <param name="strExamPlaceID"></param>
        /// <param name="strSpecialityKindsNO"></param>
        /// <returns></returns>
        public string CreateExamLotNO(string strExamPlaceNO, string strExamLevel, string strSpecialityKindsNO, string strExamYear)
        {
            string strAppNo = string.Empty;
            string strLotNo_Prefix = "";
            string strLotNo_Len = "";
            DataTable dtGroup = DbHelper.GetDatabase().From<TB_Sys_ExamPlaceSpecialityKindsGroupMapping>().Where(
                TB_Sys_ExamPlaceSpecialityKindsGroupMapping._.ExamPlaceNO == strExamPlaceNO
                && TB_Sys_ExamPlaceSpecialityKindsGroupMapping._.ExamLevel == strExamLevel
                && TB_Sys_ExamPlaceSpecialityKindsGroupMapping._.SpecialityKindsNO == strSpecialityKindsNO
                && TB_Sys_ExamPlaceSpecialityKindsGroupMapping._.ExamYear == strExamYear).ToDataTable();
            if (dtGroup != null && dtGroup.Rows.Count > 0)
            {
                strLotNo_Prefix = dtGroup.Rows[0]["LotNo_Prefix"].ToString();
                strLotNo_Len = dtGroup.Rows[0]["LotNo_Len"].ToString();
            }
            if (strLotNo_Len.Length == 0 || strLotNo_Len == "0")
            {
                strLotNo_Len = "4";
            }
            string strSerNumber = CreateExamLotNoData(strExamPlaceNO, strExamLevel, strSpecialityKindsNO, strExamYear);
            if (strLotNo_Prefix.Length > 0)
            {
                strAppNo = strLotNo_Prefix + "" + strSerNumber.PadLeft(strLotNo_Len.ToSafeInt(), '0');
            }
            else
            {
                strAppNo = strSerNumber.PadLeft(strLotNo_Len.ToSafeInt(), '0');
            }
            return strAppNo;
        }

        /// <summary>
        /// 生成抽签号,开始号和最大号
        /// </summary>
        /// <param name="appointmentgroupid"></param>
        /// <returns></returns>
        public string CreateLotteryNO(string appointmentgroupid)
        {
            var result = "";
            TB_Sys_ExamPlaceSpecialityKindsGroupMapping appointment = DbHelper.GetDatabase().From<TB_Sys_ExamPlaceSpecialityKindsGroupMapping>().Where(
                TB_Sys_ExamPlaceSpecialityKindsGroupMapping._.GroupNo == appointmentgroupid).ToFirst();
            if (appointment != null)
            {
                string skNO = appointment.SpecialityKindsNO;
                int lenth = appointment.LotNo_Len.ToSafeInt();
                string strPrefix = appointment.LotNo_Prefix;
                var TotalAcount = appointment.TotalAcount ?? 60;
                int BeginNum = appointment.BeginNum.ToSafeInt();
                string NORecord = appointment.NORecord ?? "";
                var currentLogNo = "";
                do
                {
                    Random ra = new Random();
                    var lottery = ra.Next(BeginNum, (TotalAcount + BeginNum)).ToString().PadLeft(lenth, '0');
                    currentLogNo = result + lottery;

                } while (!string.IsNullOrEmpty(currentLogNo) && NORecord.Contains(currentLogNo));

                result = currentLogNo;

                return strPrefix + result;
            }
            return result;
        }

        /// <summary>
        /// 根据考点和专业生成准考证号
        /// </summary>
        /// <param name="strExamPlaceID"></param>
        /// <param name="strSpecialityKindsNO"></param>
        /// <returns></returns>
        internal string CreateExamAllowExamIdData(string strExamPlaceID, string strSpecialityKindsNO, string strExamYear)
        {
            string strValue = string.Empty;
            DbParameter[] parameters = new DbParameter[]
                {
                    new SqlParameter("@ExamPlaceNO", SqlDbType.NVarChar,50),
                    new SqlParameter("@SpecialityKindsNO",SqlDbType.NVarChar,50),
                    new SqlParameter("@ExamYear", SqlDbType.NVarChar,50)
                };
            parameters[0].Value = strExamPlaceID;
            parameters[1].Value = strSpecialityKindsNO;
            parameters[2].Value = strExamYear;
            strValue = DbHelper.GetDatabase().FromProc("PRO_CreateExamAppNo").AddParameter(parameters).ToScalar<string>();
            return strValue;
        }

        /// <summary>
        /// 生成订单编号
        /// </summary>
        /// <returns></returns>
        internal DataTable CreateOrderNoData()
        {
            return DbHelper.GetDatabase().FromProc("PRO_CreateOrderAppNo").ToDataTable();
        }

        internal string CreateExamLotNoData(string strExamPlaceNO, string strExamLevel, string strSpecialityKindsNO, string strExamYear)
        {
            string strValue = string.Empty;
            DbParameter[] parameters = new DbParameter[]
                {
                    new SqlParameter("@ExamPlaceNO", SqlDbType.NVarChar,50),
                    new SqlParameter("@SpecialityKindsNO",SqlDbType.NVarChar,50),
                    new SqlParameter("@ExamLevel",SqlDbType.NVarChar,50),
                    new SqlParameter("@ExamYear", SqlDbType.NVarChar,50)
                };
            parameters[0].Value = strExamPlaceNO;
            parameters[1].Value = strSpecialityKindsNO;
            parameters[2].Value = strExamLevel;
            parameters[3].Value = strExamYear;
            strValue = DbHelper.GetDatabase().FromProc("PRO_CreateLotNoAppNo").AddParameter(parameters).ToScalar<string>();
            return strValue;
        }


        #region 获取 生源地信息
        public DataTable GetExamProvinceDataTable()
        {
            return DbHelper.GetDatabase().From<TB_Dic_ExamProvince>() //选择表
                                       .LeftJoin<TB_Dic_NMT_SignUp>(TB_Dic_ExamProvince._.DicNO == TB_Dic_NMT_SignUp._.NativePlaceNO)
                                       .Select(TB_Dic_ExamProvince._.All, TB_Dic_NMT_SignUp._.Index_Begin, TB_Dic_NMT_SignUp._.Index_End, TB_Dic_NMT_SignUp._.Mask, TB_Dic_NMT_SignUp._.NMT_SignUp_Len, TB_Dic_NMT_SignUp._.NativePlaceNO, TB_Dic_NMT_SignUp._.NativePlaceName)  //设置选择的字段数目 
                                       .Where(TB_Dic_ExamProvince._.IsActive == "1")
                                       .ToDataTable();   //返回List

        }
        #endregion
    }
}
